<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2021/1/27
 * Time: 19:25
 */

# 载入composer自动加载文件 require 瑞块儿
require_once(dirname(__FILE__) . '/../../lib/vendor/autoload.php');
require_once(dirname(__FILE__) . '/../../inc/config.inc.php');
require_once(dirname(__FILE__) . '/../../function.php');

// 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

//设置字体颜色
//设置对齐方式
//设置Border样式
//数字格式化
//设置背景色


$data = [];
$sql = "SELECT *  FROM `#@__task` where checkinfo=1 and is_delete=0  order by classid asc  ";
$new_item = [];
$top_title = "";
$dosql->Execute($sql);
$i = 1;
while ($row = $dosql->GetArray()) {
    $row['idd'] = $i;
    $row['typename'] = getTypeName($row['classid']);

    if ($row['unit'] != "") {
        $row['value'] .= " / " . $row['unit'];
    }

    $row_arr['data'][] = $row;
    $i++;
    $new_item[] = $row;

}


$cellKey = array(
    'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
    'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
    'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM',
    'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
);

// 表的项目
$cellName = array(
    'id' => array('序号', 'idd', 8), //列名称，索引值，宽度
    'typename' => array('类别', 'typename', 18), //列名称，索引值，宽度
    'taskname' => array('项目名称', 'title', 30), //列名称，索引值，宽度
    'fee' => array('计分标准', 'value', 12),
    'remark' => array('备注', 'remark', 30),

);


$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$spreadsheet->getActiveSheet()->setTitle('奖励分类');

//将A1到 ?1 合并成一个单元格
$spreadsheet->getActiveSheet()->mergeCells('A1:' . $cellKey[count($cellName) - 1] . '1');
$sheet->setCellValue('A1', "测试表格导出");
//水平居中
$styleArray = [
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
];

$sheet->getStyle('A1')->applyFromArray($styleArray);
$sheet->getRowDimension(1)->setRowHeight(50);


$sheet->getStyle('A1')->getFont()->setBold(true)->setName('Arial')
    ->setSize(16);
$sheet->getStyle('A1')->getFont()->setBold(true);

//设置默认行高
//$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);

$topNumber = 2;// 表头 在第几行




//处理表头
$i = 0;
foreach ($cellName as $k => $v) {
    $sheet->setCellValue($cellKey[$i] . $topNumber, $v[0]);
    $sheet->getColumnDimension($cellKey[$i])->setWidth($v[2]);
    $sheet->getStyle($cellKey[$i] . $topNumber)->applyFromArray($styleArray);;
    $sheet->getRowDimension($topNumber)->setRowHeight(24); //当前行高

    $i++;
}

//表头加粗
$sheet->getStyle('A'.$topNumber.':'.$cellKey[count($cellName) - 1] . $topNumber)->getFont()->setBold(true)->setName('Arial')->setSize(12);
$sheet->getStyle('A'.$topNumber.':'.$cellKey[count($cellName) - 1] . $topNumber)->getFont()->setBold(true);


//循环赋值
$k = 2;
foreach ($new_item as $k => $v) {

    // 当前行数值
    $now_line = $k + 1 + $topNumber;
    $ii = 0;
    foreach ($cellName as $k1 => $v1) {
        $sheet->setCellValue($cellKey[$ii] . $now_line, $v[$v1[1]]);


        $ii++;
    }
    $sheet->getRowDimension($now_line)->setRowHeight(24);

    $k++;
}


$cellName_num = count($cellName);
$lastCell = $cellKey[$cellName_num - 1]; //获取最后一行的 excel 字母
$styleArray1 = [
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
        ]
    ]
];
//添加边框
$sheet->getStyle('A1' . ':' . $lastCell . $now_line)->applyFromArray($styleArray1);
//垂直居中
$sheet->getStyle('A1' . ':' . $lastCell . $now_line)->getAlignment()->setVertical(Alignment::HORIZONTAL_CENTER);

$file_name = date('Y-m-d', time()) . rand(1000, 9999);
//第一种保存方式
/*$writer = new Xlsx($spreadsheet);
//保存的路径可自行设置
$file_name = '../'.$file_name . ".xlsx";
$writer->save($file_name);*/
//第二种直接页面上显示下载
ob_end_clean();
$file_name = $file_name . ".xlsx";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $file_name . '"');
header('Cache-Control: max-age=0');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//注意createWriter($spreadsheet, 'Xls') 第二个参数首字母必须大写
$writer->save('php://output');